﻿
CREATE PROCEDURE [dbo].[proc_Statistics_SSRB]
	@sRq datetime,
	@eRq datetime
AS
BEGIN
	Declare @Yf varchar(7)
	Set @sRq= CONVERT(varchar(10),@sRq,120)+' 00:00:00'
	Set @eRq= CONVERT(varchar(10),@eRq,120)+' 23:59:59'
	If CONVERT(varchar(7),@sRq,120)<> CONVERT(varchar(7),@eRq,120) 
	Begin
		Return
	End
	--实收日报累计
	--所有公司
	--Select A.id,A.pid,A.name,B.CompanyId,
	--	B.IAA,B.IAB,B.qqqdfqzcml,B.fyze,B.sscl from 
	--	(Select id,pid,name,CompanyId from Zzjg  where pid in (12,13,14,281,282) and CompanyId not in(23,129)) A,
	--	(Select CompanyId, Sum(IAA) IAA,Sum(IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(fyze) fyze,Sum(sscl) sscl
	--		From Statistics_Financial Where CreateDate between @sRq and @eRq
	--		group by CompanyId) B 
	--Where A.CompanyId=B.CompanyId  
	--管理区与合计
	--龙采管理区
	Select 1 ro,A.pid,Zzjg.name,Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
		(Select id,pid,name,CompanyId from Zzjg where pid in (12,13,14) and CompanyId not in(23,129) ) A 
		left join 
		(Select CompanyId, Sum(IAA) IAA,Sum(IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(fyze) fyze,Sum(sscl) sscl
			From Statistics_Financial Where CreateDate between @sRq and @eRq
			group by CompanyId) B on A.CompanyId=B.CompanyId 
		left join Zzjg on A.pid=Zzjg.id
		left join 
		(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
			where Deleted=0 and RegulateDate between @sRq and @eRq and Approval=1
			Group by CompanyId) p on A.CompanyId=p.CompanyId
	Group by A.pid,Zzjg.name
	union
	--龙采管理区合计
	Select 2 ro,99,'合计',Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg where pid in (12,13,14) and CompanyId not in(23,129) ) A
			left join 
			(Select CompanyId, Sum(IAA) IAA,Sum(IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(fyze) fyze,Sum(sscl) sscl
				From Statistics_Financial Where CreateDate between @sRq and @eRq
				group by CompanyId) B on A.CompanyId=B.CompanyId 
			left join Zzjg on A.pid=Zzjg.id
			left join 
			(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and RegulateDate between @sRq and @eRq and Approval=1
				Group by CompanyId) p on A.CompanyId=p.CompanyId
	union
	--资海管理区
	Select 3 ro,A.pid,Zzjg.name,Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg where pid in (281,282)  ) A
			left join 
			(Select CompanyId, Sum(IAA) IAA,Sum(IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(fyze) fyze,Sum(sscl) sscl
				From Statistics_Financial Where CreateDate between @sRq and @eRq
				group by CompanyId) B on A.CompanyId=B.CompanyId 
			left join Zzjg on A.pid=Zzjg.id
			left join 
			(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and RegulateDate between @sRq and @eRq and Approval=1
				Group by CompanyId) p on A.CompanyId=p.CompanyId
		Group by A.pid,Zzjg.name
	union
	--资海管理区合计
	Select 4 ro,999,'合计',Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg where pid in (281,282) ) A
			left join 
			(Select CompanyId, Sum(IAA) IAA,Sum(IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(fyze) fyze,Sum(sscl) sscl
				From Statistics_Financial Where CreateDate between @sRq and @eRq
				group by CompanyId) B on A.CompanyId=B.CompanyId 
			left join Zzjg on A.pid=Zzjg.id
			left join 
			(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and RegulateDate between @sRq and @eRq and Approval=1
				Group by CompanyId) p on A.CompanyId=p.CompanyId
	union
	--总计
	Select 5 ro,9999,'总计',Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg where pid in (12,13,14,281,282) and CompanyId not in(23,129) ) A
			left join 
			(Select CompanyId, Sum(IAA) IAA,Sum(IAB) IAB,Sum(qqqdfqzcml) qqqdfqzcml,Sum(fyze) fyze,Sum(sscl) sscl
				From Statistics_Financial Where CreateDate between @sRq and @eRq
				group by CompanyId) B on A.CompanyId=B.CompanyId 
			left join Zzjg on A.pid=Zzjg.id
			left join 
			(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and RegulateDate between @sRq and @eRq and Approval=1
				Group by CompanyId) p on A.CompanyId=p.CompanyId
	
	--实收日报当日
	--所有公司
	--Select A.id,A.pid,A.name,B.CompanyId,
	--	B.IAA,B.IAB,IBA,IBB,IBC,B.qqqdfqzcml,B.fyze,B.sscl from 
	--	(Select id,pid,name,CompanyId from Zzjg  where pid in (12,13,14,281,282) and CompanyId not in(23,129)) A,
	--	(Select CompanyId, IAA,IAB,IBA,IBB,IBC,qqqdfqzcml,fyze,sscl
	--		From Statistics_Financial Where CONVERT(varchar(10),CreateDate,120)=CONVERT(varchar(10),@eRq,120) ) B 
	--Where A.CompanyId=B.CompanyId  

	Select 1 ro,A.pid,Zzjg.name,Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(B.IBA) IBA,Sum(B.IBB) IBB,Sum(B.IBC) IBC,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
		(Select id,pid,name,CompanyId from Zzjg where pid in (12,13,14) and CompanyId not in(23,129)) A
		left join
		(Select CompanyId,IAA,IAB,IBA,IBB,IBC,qqqdfqzcml,fyze,sscl
			From Statistics_Financial Where CONVERT(varchar(10),CreateDate,120)=CONVERT(varchar(10),@eRq,120) ) B 
			on A.CompanyId=B.CompanyId 
		left join Zzjg on A.pid=Zzjg.id
		left join 
		(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and CONVERT(varchar(10),RegulateDate,120)=CONVERT(varchar(10),@eRq,120) and Approval=1
			Group by CompanyId) p on A.CompanyId=p.CompanyId
	Group by A.pid,Zzjg.name
	union
	Select 2 ro,99,'合计',Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(B.IBA) IBA,Sum(B.IBB) IBB,Sum(B.IBC) IBC,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg where pid in (12,13,14) and CompanyId not in(23,129)) A
			left join
			(Select CompanyId,IAA,IAB,IBA,IBB,IBC,qqqdfqzcml,fyze,sscl
				From Statistics_Financial Where CONVERT(varchar(10),CreateDate,120)=CONVERT(varchar(10),@eRq,120) ) B 
				on A.CompanyId=B.CompanyId 
			left join Zzjg on A.pid=Zzjg.id
			left join 
			(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and CONVERT(varchar(10),RegulateDate,120)=CONVERT(varchar(10),@eRq,120) and Approval=1
			Group by CompanyId) p on A.CompanyId=p.CompanyId

	union
	Select 3 ro,A.pid,Zzjg.name,Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(B.IBA) IBA,Sum(B.IBB) IBB,Sum(B.IBC) IBC,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg where pid in (281,282) ) A
			left join
			(Select CompanyId,IAA,IAB,IBA,IBB,IBC,qqqdfqzcml,fyze,sscl
				From Statistics_Financial Where CONVERT(varchar(10),CreateDate,120)=CONVERT(varchar(10),@eRq,120) ) B 
				on A.CompanyId=B.CompanyId 
			left join Zzjg on A.pid=Zzjg.id
			left join 
			(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and CONVERT(varchar(10),RegulateDate,120)=CONVERT(varchar(10),@eRq,120) and Approval=1
			Group by CompanyId) p on A.CompanyId=p.CompanyId
		Group by A.pid,Zzjg.name
	union
	Select 4 ro,999,'合计',Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(B.IBA) IBA,Sum(B.IBB) IBB,Sum(B.IBC) IBC,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg where pid in (281,282) ) A
			left join
			(Select CompanyId,IAA,IAB,IBA,IBB,IBC,qqqdfqzcml,fyze,sscl
				From Statistics_Financial Where CONVERT(varchar(10),CreateDate,120)=CONVERT(varchar(10),@eRq,120) ) B 
				on A.CompanyId=B.CompanyId 
			left join Zzjg on A.pid=Zzjg.id
			left join 
			(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and CONVERT(varchar(10),RegulateDate,120)=CONVERT(varchar(10),@eRq,120) and Approval=1
			Group by CompanyId) p on A.CompanyId=p.CompanyId
	union
	Select 5 ro,9999,'总计',Sum(B.IAA) IAA,Sum(B.IAB) IAB,Sum(B.IBA) IBA,Sum(B.IBB) IBB,Sum(B.IBC) IBC,Sum(qqqdfqzcml) qqqdfqzcml,Sum(B.fyze) fyze,Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg where pid in (12,13,14,281,282) and CompanyId not in(23,129)) A
			left join
			(Select CompanyId,IAA,IAB,IBA,IBB,IBC,qqqdfqzcml,fyze,sscl
				From Statistics_Financial Where CONVERT(varchar(10),CreateDate,120)=CONVERT(varchar(10),@eRq,120) ) B 
				on A.CompanyId=B.CompanyId 
			left join Zzjg on A.pid=Zzjg.id
			left join 
			(Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
				where Deleted=0 and CONVERT(varchar(10),RegulateDate,120)=CONVERT(varchar(10),@eRq,120) and Approval=1
			Group by CompanyId) p on A.CompanyId=p.CompanyId
END
